* Replication code for Hirst & Robertson (2022)
* File 3 of 4 
* This file was written for Stata 16.1 and has not been tested on any other version.
* Please read the README file before continuing. 

clear all
cd "C:\your\data\directory\" //Change this to where you are storing the files
local date FINAL

/*INTERMEDIATE DATA IN 
"Voting_combined" //from 01_PrepareRecordDate 
"MeetingLevel" //from 01_PrepareRecordDate 
"U2_available" // from 02_Lending	
"POS_units" // from 02_Lending
*/

/*Final Clean up*/
if 1 == 1 {
	// Go back to ISS data 
	use "Voting_combined",  clear
	keep if SHRCD_10_11 == 1 | SHRCD_10_11  == .
	keep if SHRCD_10_11 == 1 	
	drop _merge
	merge m:1 MeetingID using "U2_available"	
	drop _merge
	merge m:1 MeetingID using "POS_units"	
	drop _merge
	encode AgendaGeneralDesc, gen(AgendaGeneralDesc_group)
	encode cusip6, gen(cusip6_group)


	//Construct support metrics 
	gen double denominator = 0
	foreach var in outstandingShare votedFor votedAgainst votedAbstain brokerNonVote VotedWithheld {
	    replace `var' = 0 if `var' == . //Fill in zeros
		}
	replace denominator = votedFor + votedAgainst + votedAbstain + brokerNonVote + VotedWithheld ///
		if base == "Votes Represent"  
	replace denominator = votedFor + votedAgainst + votedAbstain + brokerNonVote + VotedWithheld ///
		if base == "Capital Represe" 
	replace denominator = votedFor + votedAgainst + VotedWithheld if base == "F A"
	replace denominator = votedFor + votedAgainst + VotedWithheld if base == "F+A"
	replace denominator = votedFor + votedAgainst + votedAbstain + VotedWithheld ///
		if base ==  "F A AB"
	replace denominator = votedFor + votedAgainst + votedAbstain + VotedWithheld ///
		if base == "F+A+AB"
	replace denominator = votedFor + votedAgainst + votedAbstain + VotedWithheld ///
		if base == "F+A+B"
	replace denominator = outstandingShare if base == "Outstanding" 
	replace denominator = votedFor + votedAgainst if base == "NA" 

	gen double vote_for_pct = votedFor / denominator 
	replace vote_for_pct = 1 if vote_for_pct >1
	replace voteRequirement = .666 if voteRequirement == 66.6
	gen pass = 0
	replace pass = 1 if vote_for_pct > voteRequirement & voteRequirement >=.5
	replace pass = 1 if vote_for_pct > .5 & voteRequirement <.5
	replace pass = . if vote_for_pct == .
	foreach x in "Not Applicable" "Not Disclosed" "Pending" "Withdrawn" {
		replace pass = . if voteResult == "`x'"
		}
	tab voteResult pass 

	gen vote_margin = . 
	replace vote_margin = abs(votedFor - (voteRequirement*denominator))	 
	replace vote_margin = . if voteRequirement < .5
	replace vote_margin = . if ISSAgendaItemID == "M0552" //this is advisory vote on SOP frequency -- it doesn't really make sense here so we set it to missing
	gen mgt_won = .
	replace mgt_won = 1 if MGMTrec == "For" & voteResult == "Pass"
	replace mgt_won = 1 if MGMTrec == "Against" & voteResult == "Fail"
	replace mgt_won = 0 if MGMTrec == "Against" & voteResult == "Pass"
	replace mgt_won = 0 if MGMTrec == "For" & voteResult == "Fail"
	replace mgt_won = 0 if MGMTrec == "For" & voteResult == "Withdrawn"
	
	gen ISS_won = .
	replace ISS_won = 1 if ISSrec == "For" & voteResult == "Pass"
	replace ISS_won = 1 if ISSrec == "Against" & voteResult == "Fail"
	replace ISS_won = 1 if ISSrec == "Against" & voteResult == "Withdrawn"
	replace ISS_won = 1 if ISSrec == "Withhold" & voteResult == "Fail"
	replace ISS_won = 1 if ISSrec == "Withhold" & voteResult == "Withdrawn"
	replace ISS_won = 0 if ISSrec == "Against" & voteResult == "Pass"
	replace ISS_won = 0 if ISSrec == "For" & voteResult == "Fail"
	replace ISS_won = 0 if ISSrec == "For" & voteResult == "Withdrawn"
	replace ISS_won = 0 if ISSrec == "Withhold" & voteResult == "Pass"
	
	gen ISS_mgt_won = ISS_won * mgt_won


	
	gen close = 0 
	replace close = 1 if abs(vote_for_pct - voteRequirement) <=.1 & voteRequirement >=.5
	replace close = 1 if vote_for_pct < .6  & voteResult == "Pass" & voteRequirement <.5
	replace close = 1 if vote_for_pct > .4  & voteResult == "Fail" & voteRequirement <.5
	replace close = . if voteRequirement == .
	replace close = . if ISSAgendaItemID == "M0552" //"Close" doesn't really make sense here (advisory vote on SOP frequency) - we set it to missing
	
	gen close_won = close * mgt_won
	
	gen ISS_mgt_agree = 0
	foreach var in "Abstain" "Against" "Do Not Vote" "For" "None" "Withhold" {
		replace ISS_mgt_agree = 1 if MGMTrec == "`var'" & ISSrec == "`var'"
		} 
	foreach var in "One Year" "Two Years" "Three Years" "Refer" {
		replace ISS_mgt_agree = . if MGMTrec == "`var'" | ISSrec == "`var'"
		} 

	gen sh_prop = 0 
	replace sh_prop = 1 if substr(ISSAgendaItemID,1,1)=="S" 
	
	gen proxy_contest = 0 
	replace proxy_contest= 1 if ISSAgendaItemID == "M0297" 
	replace proxy_contest= 1 if ISSAgendaItemID == "M0299" 
	replace proxy_contest= 1 if ISSAgendaItemID == "S0214" 
	replace proxy_contest= 1 if ISSAgendaItemID == "S0297" 
	replace proxy_contest= 1 if ISSAgendaItemID == "S0299" 
	gen merger = 0 
	foreach var in "M0145" "M0405" "M0406" "M0409" "M0410" {
		replace merger = 1 if ISSAgendaItemID == "`var'" 
		}
	
	gen ISS_mgt_disagree = 0 
	replace ISS_mgt_disagree = 1 if ISS_mgt_agree == 0 
	replace ISS_mgt_disagree = . if ISS_mgt_agree == .



	gen lent_margin = lent_pre_1 - vote_margin
	label var lent_margin "lent_pre_1 - vote_margin"
	replace lent_margin = . if votedFor == 0
	replace lent_margin = . if ISSAgendaItemID == "M0552" // (advisory vote on SOP frequency) - we set it to missing
	replace lent_margin = . if vote_margin == .
	
	gen lent_pivotal = 0 
	replace lent_pivotal = 1 if lent_margin >0
	replace lent_pivotal = . if lent_pre_1 == .
	replace lent_pivotal = . if lent_margin == .
	gen lent_margin1 = lent_pre_1 / vote_margin 
	replace lent_margin1 = . if votedFor == 0
	replace lent_margin1 = . if ISSAgendaItemID == "M0552" // (advisory vote on SOP frequency)  
	label var lent_margin1 "lent_pre_1 / vote_margin"
	sum lent_margin1 , det 
	gen lent_ratio = .
	forvalues i = 1 / 10 {
		replace lent_ratio = `i' if lent_margin1 >= `i' & lent_margin1 < (`i'+1)
		}
	replace lent_ratio = 10 if lent_margin1 >10
	replace lent_ratio = . if lent_margin1 == .
	label var lent_ratio "Ratio of lent to winning margin (dummies)"

	tab lent_pivotal proxy_before, row col
	drop if ISSAgendaItemID == "M0552" // (advisory vote on SOP frequency) - we drop these

	keep if SHRCD_10_11 == 1 
	keep if MeetingType == "Annual" | MeetingType == "Proxy Contest" |MeetingType == "Special"
	save "RecordData_all", replace
}
 

*Make Table 2*
if 1 == 1 {
	use "RecordData_all", clear 
	keep if mgt_won != . & proxy_before !=.	//This line drops observations that should not be here 
	keep if SHRCD_10_11 == 1 
	gen no_proxy_before = 0
	replace no_proxy_before = 1 if proxy_before == 0
	replace no_proxy_before = . if proxy_before == .
	replace no_proxy_before = no_proxy_before *100 
	drop proxy_before  
	gen mgt_lost = 1 if mgt_won == 0 
	replace mgt_lost = 0 if mgt_won == 1 
	replace mgt_lost = . if mgt_won == . 
	
	gen ISS_lost = 1 if ISS_won == 0 
	replace ISS_lost = 0 if ISS_won == 1 
	replace ISS_lost = . if ISS_won == . 
			
	local vars close mgt_lost ISS_lost sh_prop 
	mat def B=J(40,3,.)
	
	*We start with the VOTE level*
	local j = 1 
	mean no_proxy_before  
	local b = (_b[no_proxy_before])
	local v = sqrt(e(V)[1,1])
	mat B[`j',1] = round(`b',.1) 
	local j = `j' + 1 
	mat B[`j',1] = round(`v',.001) 
	local j = `j' +1
	count if no_proxy_before !=.  
	mat B[`j',1] = r(N) 
	local j = `j' + 1 

	mean no_proxy_before if MeetingType == "Annual" 
	local b = (_b[no_proxy_before])
	local v = sqrt(e(V)[1,1])
	mat B[`j',1] = round(`b',.1) 
	local j = `j' + 1 
	mat B[`j',1] = round(`v',.001) 
	local j = `j' + 1 
	count if no_proxy_before !=. & MeetingType == "Annual" 
	mat B[`j',1] = r(N) 
	local j = `j' + 1 

	mean no_proxy_before if MeetingType == "Proxy Contest" 
	local b = (_b[no_proxy_before])
	local v = sqrt(e(V)[1,1])
	mat B[`j',1] = round(`b',.1) 
	local j = `j' + 1 
	mat B[`j',1] = round(`v',.001) 
	local j = `j' + 1 
	count if no_proxy_before !=. & MeetingType == "Proxy Contest" 
	mat B[`j',1] = r(N) 
	local j = `j' + 1 

	mean no_proxy_before if MeetingType == "Special"
	local b = (_b[no_proxy_before])
	local v = sqrt(e(V)[1,1])
	mat B[`j',1] = round(`b',.1) 
	local j = `j' + 1 
	mat B[`j',1] = round(`v',.001) 
	local j = `j' + 1 
	count if no_proxy_before !=. & MeetingType == "Special" 
	mat B[`j',1] = r(N) 
	local j = `j' + 1 

		foreach var in `vars' {
		quietly{
			mean no_proxy_before if `var' == 1 & MeetingType == "Annual" 
			local b = (_b[no_proxy_before])
			local v = sqrt(e(V)[1,1])
			mat B[`j',1] = round(`b',.1) 
			local j = `j' + 1
			mat B[`j',1] = round(`v',.001) 
			local j = `j' + 1
			mean no_proxy_before if `var' == 0 & MeetingType == "Annual" 
			local b = (_b[no_proxy_before])
			local v = sqrt(e(V)[1,1])
			mat B[`j',1] = round(`b',.1) 
			local j = `j' + 1
			mat B[`j',1] = round(`v',.001) 
			local j = `j' + 1
			}
		}
	
	*Now we do the MEETING Level  
	foreach var in `vars' {
		egen max_`var' = max(`var'), by(MeetingID)
		drop `var'
		rename max_`var' `var'
		}

	bysort MeetingID: keep if _n == 1 

	local j = 1 
	mean no_proxy_before  
	local b = (_b[no_proxy_before])
	local v = sqrt(e(V)[1,1])
	mat B[`j',2] = round(`b',.1) 
	local j = `j' + 1 
	mat B[`j',2] = round(`v',.001) 
	local j = `j' +1
	count if no_proxy_before !=.  
	mat B[`j',2] = r(N) 
	local j = `j' +1

	mean no_proxy_before if MeetingType == "Annual" 
	local b = (_b[no_proxy_before])
	local v = sqrt(e(V)[1,1])
	mat B[`j',2] = round(`b',.1) 
	local j = `j' + 1 
	mat B[`j',2] = round(`v',.001) 
	local j = `j' + 1 
	count if no_proxy_before !=. & MeetingType == "Annual" 
	mat B[`j',2] = r(N) 
	local j = `j' + 1 

	mean no_proxy_before if MeetingType == "Proxy Contest" 
	local b = (_b[no_proxy_before])
	local v = sqrt(e(V)[1,1])
	mat B[`j',2] = round(`b',.1) 
	local j = `j' + 1 
	mat B[`j',2] = round(`v',.001) 
	local j = `j' + 1 
	count if no_proxy_before !=. & MeetingType == "Proxy Contest" 
	mat B[`j',2] = r(N) 
	local j = `j' + 1 

	mean no_proxy_before if MeetingType == "Special"
	local b = (_b[no_proxy_before])
	local v = sqrt(e(V)[1,1])
	mat B[`j',2] = round(`b',.1) 
	local j = `j' + 1 
	mat B[`j',2] = round(`v',.001) 
	local j = `j' + 1 
	count if no_proxy_before !=. & MeetingType == "Special" 
	mat B[`j',2] = r(N) 
	local j = `j' + 1 

	foreach var in `vars' {
		quietly{
			mean no_proxy_before if `var' == 1 & MeetingType == "Annual" 
			local b = (_b[no_proxy_before])
			local v = sqrt(e(V)[1,1])
			mat B[`j',2] = round(`b',.1) 
			local j = `j' + 1
			mat B[`j',2] = round(`v',.001) 
			local j = `j' + 1
			mean no_proxy_before if `var' == 0 & MeetingType == "Annual" 
			local b = (_b[no_proxy_before])
			local v = sqrt(e(V)[1,1])
			mat B[`j',2] = round(`b',.1) 
			local j = `j' + 1
			mat B[`j',2] = round(`v',.001) 
			local j = `j' + 1
			}
		}
	mat list B

	local j = 5
	putexcel set "Results/Table2_`date'", replace
	putexcel C1= "Vote Level"
	putexcel D1 = "Meeting Level"
	putexcel A2 = "Overall"
	putexcel A4 = "Number of Observations"
	
	foreach var in  Annual ProxyContest Special {
		putexcel A`j' = "`var' Yes" 
		local j = `j' +2
		putexcel A`j' = "`var' Number of Observations" 
		local j = `j' +1		
		}
	foreach var in `vars' {
		putexcel A`j' = "`var' Yes" 
		local j = `j' +2
		putexcel A`j' = "`var' No" 
		local j = `j' +2		
		}
	putexcel C2 = mat(B) 
	}

	
*Calculate whether the values in Table 1 are different*
if 1 == 1 {
	use "RecordData_all", clear 
	keep if mgt_won != . & proxy_before !=.	//This line drops observations that should not be here 
	keep if SHRCD_10_11 == 1 
	gen no_proxy_before = 0
	replace no_proxy_before = 1 if proxy_before == 0
	replace no_proxy_before = . if proxy_before == .
	replace no_proxy_before = no_proxy_before *100 
	drop proxy_before  
	gen mgt_lost = 1 if mgt_won == 0 
	replace mgt_lost = 0 if mgt_won == 1 
	replace mgt_lost = . if mgt_won == . 
	
	gen ISS_lost = 1 if ISS_won == 0 
	replace ISS_lost = 0 if ISS_won == 1 
	replace ISS_lost = . if ISS_won == . 
			
	local vars close mgt_lost ISS_lost sh_prop 
	
	capture log close
	log using "Results/AreTheyDifferent_`date'", text replace
	
	*We start with the VOTE level*
	foreach var in `vars' {
		di as error "This is `var'"
		ttest no_proxy_before if MeetingType == "Annual" , by(`var') unp uneq
		}

	*Now we do the MEETING Level*  
	foreach var in `vars' {
		egen max_`var' = max(`var'), by(MeetingID)
		drop `var'
		rename max_`var' `var'
		}

	bysort MeetingID: keep if _n == 1 

	foreach var in `vars' {
		di as error "This is `var'"
		ttest no_proxy_before if MeetingType == "Annual" , by(`var') unp uneq
		}
	log close
	}

	
*Make Table 3*
if 1 == 1 {
	use "RecordData_all", clear 
	keep if lent_margin !=.
	count 
	count if proxy_before == 0

	bysort MeetingType: tab lent_pivotal proxy_before
	bysort MeetingType proxy_before: tab lent_pivotal 
	
	bysort MeetingType : tab lent_pivotal if proxy_before ==0
	bysort MeetingType : sum lent_margin1 if lent_pivotal == 1 & proxy_before ==0
	bysort MeetingType : sum lent_margin1 if lent_pivotal == 1 & proxy_before ==0, det
	bysort MeetingType : sum lent_margin1 if lent_pivotal == 1 & proxy_before ==0 & lent_margin1 >2 , det
	
	bysort MeetingType : tab lent_ratio if proxy_before ==0 
		
	keep if lent_ratio !=.
	count 
	count if proxy_before == 0
	
	
	*Make the table*
	mat def B=J(16,2,.)
	local j = 1 
	forvalues i = 1/5 {
		mat B[`j',1] = `i' 
		count if lent_ratio >= `i' & proxy_before ==0 & MeetingType == "Annual" & lent_ratio !=.
		mat B[`j',2] = r(N) 
		local j = `j' + 1 
		}
	forvalues i = 1/5 {
		mat B[`j',1] = `i' 
		count if lent_ratio >= `i' & proxy_before ==0 & MeetingType == "Proxy Contest" & lent_ratio !=.
		mat B[`j',2] = r(N) 
		local j = `j' + 1 
		}
	forvalues i = 1/5 {
		mat B[`j',1] = `i' 
		count if lent_ratio >= `i' & proxy_before ==0 & MeetingType == "Special" & lent_ratio !=.
		mat B[`j',2] = r(N) 
		local j = `j' + 1 
		}
	mat list B

	putexcel set "Results/Table3_`date'", replace
	putexcel B1= "Lent Ratio (at least)"
	putexcel C1= "Number of Votes"

	local j = 2
	foreach var in  Annual ProxyContest Special {
		putexcel A`j' = "`var'"   
		local j = `j' +5
		}
	putexcel B2 = mat(B) 
	}

	
*Additional Statements in Part II*
if 1 == 1 {
	use "RecordData_all", clear 
	keep if mgt_won != . & proxy_before !=.	//This line drops observations that should not be here 
	keep if SHRCD_10_11 == 1 
*	bysort MeetingID: gen first = 1 if _n == 1 
	gen no_proxy_before = 0
	replace no_proxy_before = 1 if proxy_before == 0
	replace no_proxy_before = . if proxy_before == .
	replace no_proxy_before = no_proxy_before *100 
	drop proxy_before  
	gen mgt_lost = 1 if mgt_won == 0 
	replace mgt_lost = 0 if mgt_won == 1 
	replace mgt_lost = . if mgt_won == . 
	
	gen ISS_lost = 1 if ISS_won == 0 
	replace ISS_lost = 0 if ISS_won == 1 
	replace ISS_lost = . if ISS_won == . 
	drop if no_proxy_before ==. 

	*Calculate how many CUSIPS
	if 0 == 1 {
		distinct cusip8 if no_proxy_before !=.
		}
		
	*Calculate how long between end of fiscal year and proxy
	if 0 == 1 { 
*	    preserve 
			//We do this by starting with the first day of the following month and subtracting 1
			gen fyr_1 = fyr + 1 //This gives us the next month 
			replace fyr_1 = 1 if fyr_1 == 13 // This moves December of year t to January of t+1 (we will adjust the year below)
			gen fyear_1 = fyear 
			replace fyear_1 = fyear_1 +1 if fyr == 12 // here's the t+1 part 
			gen fiscal_year = mdy(fyr_1,1,fyear) - 1 //Now do back a day 
			format fiscal_year %td
			drop fyr_1 fyear_1
			tempfile temp 
			save `temp', replace 
			
			use "MeetingLevel", clear // Extract the date of the first filing 
			egen first_proxy = min(date), by(MeetingID) 
			format first_proxy %td
			keep first_proxy MeetingID 
			duplicates drop
			duplicates r MeetingID
			
			merge 1:m MeetingID using `temp'
			keep if _merge == 3 
			keep MeetingID first_proxy fiscal_year 
			duplicates drop 
			duplicates r MeetingID
			
			gen gap =  first_proxy - fiscal_year
			replace gap = . if gap <=0 // Clearly if the proxy was filed before the end of the fiscal year, it's referring to the wrong year
			replace gap = . if gap >365 // Clearly if the gap is more than a year, this must be referring to another year
			
			sum gap, det
			restore
		}
		
	*Calculate how many special meetings are called by management
	if 0 == 1 {
		preserve 
			keep if MeetingType == "Special" 
			tab sponsor
			edit if sponsor == "NULL"
			tab MeetingID if sponsor == "NULL"
			tab sponsor if MeetingID == 1410153
			edit if MeetingID == 1410153
			restore 
		}	
	
	*Calculate how many special meetings involve mergers
	if 0 == 1 {
		preserve 
			egen merger1 = max(merger), by(MeetingID)
			bysort MeetingID: keep if _n == 1 
			drop merger
			rename merger1 merger
			count if MeetingType == "Special" & merger == 1 
			count if MeetingType == "Special" & merger != 1 
			count if MeetingType == "Special" & merger == 0 
			tab merger if MeetingType == "Special" 
			restore
		}
	
	*Calculate how many meetings (of all types) have hidden agendas
	if 0 == 1 {
		distinct MeetingID if no_proxy_before ==100 
		*check a second way (belt and suspenders)
		preserve 
			bysort MeetingID: keep if _n == 1 
			tab no_proxy_before 
			restore
		}
		
	*Calculate how many of the hidden agenda meetings are annual
	if 0 == 1 {
		distinct MeetingID if no_proxy_before ==100 & MeetingType == "Annual" 
		*check a second way (belt and suspenders)
		preserve 
			bysort MeetingID: keep if _n == 1 
			tab MeetingType if no_proxy_before == 100
			restore
		}

	*Calculate how many of the companies and meetings are NYSE or NYSE American
	if 1 == 1 {
	    gen NYSE_all = 0
		replace NYSE_all = 1 if PRIMEXCH_N ==1 | PRIMEXCH_A == 1
	    *Companies
		distinct cusip8 
		distinct cusip8 if NYSE_all == 1
		*check a second way (belt and suspenders)
		preserve 
			bysort cusip8: keep if _n == 1 
			tab NYSE_all
			restore
	    *Meetings
		distinct MeetingID 
		distinct MeetingID if NYSE_all == 1
		*check a second way (belt and suspenders)
		preserve 
			bysort MeetingID: keep if _n == 1 
			tab NYSE_all
			tab PRIMEXCH_N 
			tab PRIMEXCH_A  
			restore
		}
	}
	 
	 
*Additional Statements in Part IV*
if 1 == 1 {
	use "MeetingLevel", clear // Extract number of days between record date and first filing 
	egen first_proxy = min(date), by(MeetingID) 
	format first_proxy %td
	keep first_proxy MeetingID 
	duplicates drop
	duplicates r MeetingID
	tempfile temp 
	save `temp', replace
	
	
	use "RecordData_all", clear 
	keep if mgt_won != . & proxy_before !=.	//This line drops observations that should not be here 
	keep if SHRCD_10_11 == 1 
*	bysort MeetingID: gen first = 1 if _n == 1 
	gen no_proxy_before = 0
	replace no_proxy_before = 1 if proxy_before == 0
	replace no_proxy_before = . if proxy_before == .
	replace no_proxy_before = no_proxy_before *100 
	drop proxy_before  
	gen mgt_lost = 1 if mgt_won == 0 
	replace mgt_lost = 0 if mgt_won == 1 
	replace mgt_lost = . if mgt_won == . 
	
	gen ISS_lost = 1 if ISS_won == 0 
	replace ISS_lost = 0 if ISS_won == 1 
	replace ISS_lost = . if ISS_won == . 
	drop if no_proxy_before ==. 


	*Calculate how long between filing and record date
	if 1 == 1 {
		merge m:1 MeetingID using `temp'
		keep if _merge == 3 
		keep MeetingID Recorddate first_proxy
		duplicates drop
		duplicates r MeetingID		
		gen gap =  Recorddate - first_proxy
		sum gap, det

		}
	}

	